import os
import sys

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from typing import List
from src.tasks.enterpriseToEs.EntEnterprise import EntEnterprise
from src.utils import CommonUtils
from src.base import QeRankMapping


class SyncEnterpriseRatingData:
    def __init__(self, logger, localDb, dataCenterDb, fcdb):
        self.logger = logger
        self.localDb = localDb
        self.dataCenterDb = dataCenterDb
        self.fcdb = fcdb
        self.size = 1000
        self.abroadRatingOrgCode = ['80065840', '80058815', '80065841']
        self.abroadRatingOrg = ['惠誉国际信用评级有限公司', '标准普尔评估有限公司', '穆迪投资者服务公司']
        self.outerRatingOrgCode = ['80002154', '80002172', '80002192', '80062388', '80002101', '80000858', '80129270', '80117870', '80002099']
        self.outerRatingOrg = ['中诚信国际信用评级有限责任公司', '联合资信评估股份有限公司', '大公国际资信评估有限公司', '上海新世纪资信评估投资服务有限公司',
                               '上海远东资信评估有限公司', '中诚信证券评估有限公司', '联合信用评级有限公司', '东方金诚国际信用评估有限公司', '中证鹏元资信评估股份有限公司']

        self.exptRating = {'1': '正面', '2': "稳定", '3': "负面",
                           '4': "列入评级观察(可能调高)", '5': "列入评级观察(可能调低)", '6': "列入评级观察(走势不明)", '7': "待决",
                           '9': "无"}

    # 抽取CCX评级数据
    def __queryCcxRating__(self, t002Codes, otherCodes):
        sql1 = """
                SELECT t.credit_code, t.`level` AS `rank`, t.score, t.year FROM  
                (SELECT r.*, ROW_NUMBER() OVER ( PARTITION BY credit_code ORDER  BY YEAR desc ) AS NO  
                from base_repository_rating_result r
                INNER JOIN enterprise_info_custom  c ON c.credit_code = r.credit_code
                WHERE c.type_code <> 'T002' AND c.audit_status = 1 AND c.release_status = 1 AND c.ver = 0
                AND r.`level` is not NULL AND r.credit_code in %s
                )t 
                WHERE t.no =1 
        """
        sql2 = """
                SELECT t.credit_code,  t.platform_rank as `rank`, t.platform_rank_score  as score, t.year FROM  
                (SELECT r.*, ROW_NUMBER() OVER ( PARTITION BY r.credit_code ORDER  BY YEAR desc ) AS NO 
                from  base_repository_platform_result r
                INNER JOIN enterprise_info_custom  c ON c.credit_code = r.credit_code
                WHERE c.type_code ='T002' AND c.audit_status = 1 AND c.release_status = 1 AND c.ver = 0
                and r.ver=0 AND r.platform_rank is not null and r.credit_code in %s
                )t 
                WHERE t.no =1    
                """

        if len(otherCodes) > 0 and len(t002Codes) > 0:
            sql = "%s union all %s" % (sql1, sql2)
            param = (otherCodes, t002Codes)
        elif len(otherCodes) > 0:
            sql = sql1
            param = (otherCodes,)
        elif len(t002Codes) > 0:
            sql = sql2
            param = (t002Codes,)
        else:
            return []

        return self.dataCenterDb.Query(sql, param)

    # 从数据中心抽取城投QE评级
    def __queryCityInvestQeRating__(self):
        sql = """ SELECT * FROM (
                 SELECT credit_code, platform_rank, year, ROW_NUMBER() OVER ( PARTITION BY  credit_code  ORDER BY YEAR DESC ) AS no
                 FROM city_invest_qe_result
                 WHERE STATUS = 1 AND ver = 0 AND platform_rank IS NOT null
                 ) t WHERE t.no = 1
        """
        return self.dataCenterDb.Query(sql)

    # 从数据中心抽取非城投类型的QE评级
    def __queryQeRating__(self):
        sql = """SELECT * FROM (
                 SELECT credit_code, level, year, ROW_NUMBER() OVER ( PARTITION BY  credit_code  ORDER BY YEAR DESC ) AS no
                 FROM qe_result
                 WHERE STATUS = 1 AND ver = 0 AND level IS NOT null
                 ) t WHERE t.no = 1
        """
        return self.dataCenterDb.Query(sql)

    def __queryOuterRating__(self, ratingOrgCodes: list):
        sql = """
            SELECT * FROM (
            SELECT COMPCODE,CREDITRATE,EXPTRATING,PUBLISHDATE,RATECOMNAME, 
            ROW_NUMBER() OVER (PARTITION by COMPCODE ORDER BY PUBLISHDATE DESC) AS no FROM TQ_BD_CREDITRTISSUE 
            WHERE RATECOMPCODE in %s
            AND RATETYPE in (1,34,44,66,85,86)
            ) t WHERE NO =1
         """
        return self.fcdb.Query(sql, (ratingOrgCodes,))

    def __updateCcxRating__(self, enterpriseList):
        # ccx评级
        self.logger.info("加载 智象评级数据")
        t002Codes = list(d.credit_code for d in enterpriseList if d.ent_type == "T002")
        otherCodes = list(d.credit_code for d in enterpriseList if not d.ent_type == "T002")
        ccxRatingDatas = self.__queryCcxRating__(t002Codes=t002Codes, otherCodes=otherCodes)
        ccxRatingDatas = {d["credit_code"]: d for d in ccxRatingDatas}
        self.logger.info("加载 智象评级数据：%d" % len(ccxRatingDatas))

        for ent in enterpriseList:
            if ent.credit_code in ccxRatingDatas.keys():
                ccxRatingData = ccxRatingDatas[ent.credit_code]
                ent.ccx_eval = ccxRatingData["rank"]
                ent.ccx_eval_info = ccxRatingData["score"]
                ent.ccx_eval_value = QeRankMapping.levelToValue(ccxRatingData["rank"])
                ent.year = ccxRatingData["year"]
            else:
                ent.ccx_eval = None
                ent.ccx_eval_info = None
                ent.ccx_eval_value = None
                ent.year = None

    def __updateQeRating__(self, enterpriseList):
        self.logger.info("加载 城投QE评级数据")
        qeRatingDatasT002 = self.__queryCityInvestQeRating__()
        qeRatingDatasT002 = {d["credit_code"]: d for d in qeRatingDatasT002}
        self.logger.info("加载 城投QE评级数据：%d" % len(qeRatingDatasT002))

        self.logger.info("加载 非城投QE评级数据")
        qeRatingDatasAll = self.__queryQeRating__()
        qeRatingDatasAll = {d["credit_code"]: d for d in qeRatingDatasAll}
        self.logger.info("加载 非城投QE评级数据：%d" % len(qeRatingDatasAll))

        # GSPJ-1486需求修正
        for ent in enterpriseList:
            if ent.ent_type == 'T002':
                # 城投
                if ent.credit_code in qeRatingDatasT002.keys():
                    # 有qe评级，使用qe评级数据
                    qeRatingData = qeRatingDatasT002[ent.credit_code]
                    ent.qe_eval = qeRatingData["platform_rank"]
                    ent.qe_sort = QeRankMapping.levelToValue(qeRatingData["platform_rank"])
                    ent.qe_year = qeRatingData["year"]
                else:
                    # 没有qe评级，置空
                    ent.qe_eval = None
                    ent.qe_sort = None
                    ent.qe_year = None
            elif ent.ent_type == 'T001' or ent.ent_type == 'T003' or ent.ent_type == 'T004':
                # 券商,银行
                if ent.credit_code in qeRatingDatasAll.keys():
                    # 有qe评级，使用qe评级数据
                    qeRatingData = qeRatingDatasAll[ent.credit_code]
                    ent.qe_eval = qeRatingData["level"]
                    ent.qe_sort = QeRankMapping.levelToValue(qeRatingData["level"])
                    ent.qe_year = qeRatingData["year"]
                else:
                    # 没有qe评级，置空
                    ent.qe_eval = None
                    ent.qe_sort = None
                    ent.qe_year = None
            else:
                # 其他类型企业，使用ccx评级替代qe评级
                ent.qe_eval = ent.ccx_eval
                ent.qe_sort = ent.ccx_eval_value
                ent.qe_year = ent.year

    def __updateOuterRating__(self, enterpriseList):
        self.logger.info("加载 外部评级数据")
        outerRatingDatas = self.__queryOuterRating__(self.outerRatingOrgCode)
        outerRatingDatas = {d["COMPCODE"]: d for d in outerRatingDatas}
        self.logger.info("加载 外部评级数据：%d " % len(outerRatingDatas))
        for ent in enterpriseList:
            if ent.comp_code in outerRatingDatas.keys():
                outerRatingData = outerRatingDatas[ent.comp_code]
                ent.external_eval = outerRatingData["CREDITRATE"]
                ent.external_eval_info = outerRatingData["EXPTRATING"]
                ent.external_eval_date = CommonUtils.strToDate(outerRatingData["PUBLISHDATE"])
                ent.external_eval_organization = outerRatingData["RATECOMNAME"]

    def __updateAroadRating__(self, enterpriseList):
        self.logger.info("加载 境外评级数据抽")
        outerRatingDatas = self.__queryOuterRating__(self.abroadRatingOrgCode)
        outerRatingDatas = {d["COMPCODE"]: d for d in outerRatingDatas}
        self.logger.info("加载 境外评级数据抽：%d " % len(outerRatingDatas))
        for ent in enterpriseList:
            if ent.comp_code in outerRatingDatas.keys():
                outerRatingData = outerRatingDatas[ent.comp_code]
                ent.foreign_external_eval = outerRatingData["CREDITRATE"]
                ent.foreign_external_eval_info = outerRatingData["EXPTRATING"]
                ent.foreign_external_eval_date = CommonUtils.strToDate(outerRatingData["PUBLISHDATE"])
                ent.foreign_external_eval_organization = outerRatingData["RATECOMNAME"]

    # 同步入口
    def update(self, enterpriseList: List[EntEnterprise]):
        self.logger.info("更新评级信息 开始")
        self.logger.info("待更新主体：%d" % len(enterpriseList))

        self.__updateCcxRating__(enterpriseList)
        self.__updateQeRating__(enterpriseList)
        self.__updateOuterRating__(enterpriseList)
        self.__updateAroadRating__(enterpriseList)

        self.logger.info("更新评级信息 完成")
